package com.ihg.hiex.admin.handler.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;

import com.ihg.hiex.admin.entity.dto.ChampionChange;
import com.ihg.hiex.admin.entity.dto.Competency;
import com.ihg.hiex.admin.entity.dto.DriveResult;
import com.ihg.hiex.admin.entity.dto.FinancialManagement;
import com.ihg.hiex.admin.entity.dto.LeadAndDevelop;
import com.ihg.hiex.admin.entity.dto.OperationsManagement;
import com.ihg.hiex.admin.entity.dto.OwnerRelations;
import com.ihg.hiex.admin.entity.dto.RevenueMaximisation;
import com.ihg.hiex.admin.entity.dto.Swift;
import com.ihg.hiex.admin.entity.dto.WorkCollaboratively;
import com.ihg.hiex.admin.handler.ParseHandler;

/**
 * 
 * @author houtao
 *
 */
@Component("parseHandler")
public class ParseHandlerImpl implements ParseHandler {
    
    private static final Logger logger = LoggerFactory.getLogger(ParseHandlerImpl.class);
    
    @Value("#{config['cloud.path']}")
    private String cloudPath;
    
    @Value("#{cloud['cloud.host']}")
    private String cloudHost;
    
    /*
     * (non-Javadoc)
     * @see com.ihg.hiex.admin.handler.ParseHandler#parseWave(java.lang.String, java.lang.String)
     */
    public Competency parseWave(String delegateEmail, String wavePath) {
        
        Competency competency = null;
        
        try {
            
            DataFormatter formatter = new DataFormatter();
            Workbook workbook = WorkbookFactory.create(this.prepareFile(wavePath));
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rows = sheet.iterator();
            
            while (rows.hasNext()) {
                
                Row row = rows.next();
                int rowNum = row.getRowNum();
                
                if (rowNum <= 0) {
                    continue;
                }
                if(delegateEmail.equalsIgnoreCase(formatter.formatCellValue(row.getCell(2)))) {
                	
                	
                	RevenueMaximisation revenueMaximisation = this.generateRm(formatter, row);
                	OperationsManagement operationsManagement = this.generateOm(formatter, row);
                	OwnerRelations ownerRelations = this.generateOr(formatter, row);
                    DriveResult driveResult = this.generateDr(formatter, row);
                    WorkCollaboratively workCollaboratively = this.generateWc(formatter, row);
                    ChampionChange championChange = this.generateCc(formatter, row);
                    LeadAndDevelop leadAndDevelop = this.generateLd(formatter, row);
                    FinancialManagement financialManagement = this.generateFm(formatter, row);
                    
                    competency = new Competency(revenueMaximisation, operationsManagement, ownerRelations, driveResult, championChange, leadAndDevelop, financialManagement, workCollaboratively);
                }
                
            }
            
            
        } catch (Exception e) {
            logger.error("##Exception when parse excel.", e);
        }
        
        return competency;
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private RevenueMaximisation generateRm(DataFormatter formatter, Row row) {
    	int qssj = Integer.valueOf(formatter.formatCellValue(row.getCell(20)));
    	int zlhjlss = Integer.valueOf(formatter.formatCellValue(row.getCell(19)));
    	int zzjh = Integer.valueOf(formatter.formatCellValue(row.getCell(52)));
    	int dxwt = Integer.valueOf(formatter.formatCellValue(row.getCell(23)));
    	int tcxf = Integer.valueOf(formatter.formatCellValue(row.getCell(24)));
    	int ttknx = Integer.valueOf(formatter.formatCellValue(row.getCell(25)));
    	int fzcl = Integer.valueOf(formatter.formatCellValue(row.getCell(26)));
    	int zdtr = Integer.valueOf(formatter.formatCellValue(row.getCell(34)));
    	
    	return new RevenueMaximisation(qssj, zlhjlss, zzjh, dxwt, tcxf, ttknx, fzcl, zdtr);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private OperationsManagement generateOm(DataFormatter formatter, Row row) {
    	int dxwt = Integer.valueOf(formatter.formatCellValue(row.getCell(23)));
    	int gzgl = Integer.valueOf(formatter.formatCellValue(row.getCell(48)));
    	int cgch = Integer.valueOf(formatter.formatCellValue(row.getCell(50)));
    	int csbz = Integer.valueOf(formatter.formatCellValue(row.getCell(49)));
    	int tcxf = Integer.valueOf(formatter.formatCellValue(row.getCell(24)));
    	int hc = Integer.valueOf(formatter.formatCellValue(row.getCell(46)));
    	int zcjc = Integer.valueOf(formatter.formatCellValue(row.getCell(33)));
    	
    	return new OperationsManagement(dxwt, gzgl, cgch, csbz, tcxf, hc, zcjc);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    public OwnerRelations generateOr(DataFormatter formatter, Row row) {
    	int ljtr = Integer.valueOf(formatter.formatCellValue(row.getCell(42)));
    	int zqfk = Integer.valueOf(formatter.formatCellValue(row.getCell(41)));
    	int yrhd = Integer.valueOf(formatter.formatCellValue(row.getCell(27)));
    	int sftr = Integer.valueOf(formatter.formatCellValue(row.getCell(30)));
    	int tdhz = Integer.valueOf(formatter.formatCellValue(row.getCell(43)));
    	int tzgd = Integer.valueOf(formatter.formatCellValue(row.getCell(32)));
    	int csbz = Integer.valueOf(formatter.formatCellValue(row.getCell(49)));
    	int jjct = Integer.valueOf(formatter.formatCellValue(row.getCell(38)));
    	
    	return new OwnerRelations(ljtr, zqfk, yrhd, sftr, tdhz, tzgd, csbz, jjct);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private DriveResult generateDr(DataFormatter formatter, Row row) {
    	
    	int cqxd = Integer.valueOf(formatter.formatCellValue(row.getCell(51)));
    	int zqmb = Integer.valueOf(formatter.formatCellValue(row.getCell(53)));
    	int gzgl = Integer.valueOf(formatter.formatCellValue(row.getCell(48)));
    	int aswcrw = Integer.valueOf(formatter.formatCellValue(row.getCell(23)));
    	int cgcc = Integer.valueOf(formatter.formatCellValue(row.getCell(50)));
    	int tcxf = Integer.valueOf(formatter.formatCellValue(row.getCell(50)));
    	int dxwt = Integer.valueOf(formatter.formatCellValue(row.getCell(23)));
        
        return new DriveResult(cqxd, zqmb, gzgl, aswcrw, cgcc, tcxf, dxwt);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private ChampionChange generateCc(DataFormatter formatter, Row row) {
    	
    	int ybbg = Integer.valueOf(formatter.formatCellValue(row.getCell(42)));
    	int lgjj = Integer.valueOf(formatter.formatCellValue(row.getCell(39)));
    	int tcxf = Integer.valueOf(formatter.formatCellValue(row.getCell(24)));
        int ttknx = Integer.valueOf(formatter.formatCellValue(row.getCell(25)));
        int sftr = Integer.valueOf(formatter.formatCellValue(row.getCell(30)));
        int tzgd = Integer.valueOf(formatter.formatCellValue(row.getCell(32)));
        int sbrts = Integer.valueOf(formatter.formatCellValue(row.getCell(35)));
        int zcjc = Integer.valueOf(formatter.formatCellValue(row.getCell(33)));
    	
        return new ChampionChange(ybbg, lgjj, tcxf, ttknx, sftr, tzgd, sbrts, zcjc);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private LeadAndDevelop generateLd(DataFormatter formatter, Row row) {
    	
    	int qxcsxx = Integer.valueOf(formatter.formatCellValue(row.getCell(31)));
    	int zdtr = Integer.valueOf(formatter.formatCellValue(row.getCell(34)));
    	int zstr = Integer.valueOf(formatter.formatCellValue(row.getCell(44)));
    	int ljtr = Integer.valueOf(formatter.formatCellValue(row.getCell(42)));
    	int sbrts = Integer.valueOf(formatter.formatCellValue(row.getCell(35)));
    	int sftr = Integer.valueOf(formatter.formatCellValue(row.getCell(30)));
    	int tdhz = Integer.valueOf(formatter.formatCellValue(row.getCell(43)));
    	
        return new LeadAndDevelop(qxcsxx, zdtr, zstr, ljtr, sbrts, sftr, tdhz);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private WorkCollaboratively generateWc(DataFormatter formatter, Row row) {
    	int jlhxgx = Integer.valueOf(formatter.formatCellValue(row.getCell(28)));
    	int yrhd = Integer.valueOf(formatter.formatCellValue(row.getCell(27)));
    	int ljtr = Integer.valueOf(formatter.formatCellValue(row.getCell(42)));
    	int zqfk = Integer.valueOf(formatter.formatCellValue(row.getCell(41)));
    	int tdhz = Integer.valueOf(formatter.formatCellValue(row.getCell(43)));
    	int sftr = Integer.valueOf(formatter.formatCellValue(row.getCell(26)));
    	int tzgd = Integer.valueOf(formatter.formatCellValue(row.getCell(32)));
    	int jjct = Integer.valueOf(formatter.formatCellValue(row.getCell(38)));
    	
        return new WorkCollaboratively(jlhxgx, yrhd, ljtr, zqfk, tdhz, sftr, tzgd, jjct);
    }
    
    /**
     * 
     * @param formatter
     * @param row
     * @return
     */
    private FinancialManagement generateFm(DataFormatter formatter, Row row) {
        
    	int qssj = Integer.valueOf(formatter.formatCellValue(row.getCell(20)));
    	int zlhjlss = Integer.valueOf(formatter.formatCellValue(row.getCell(19)));
    	int dxwt = Integer.valueOf(formatter.formatCellValue(row.getCell(23)));
        int fzzysj = Integer.valueOf(formatter.formatCellValue(row.getCell(21)));
        int tcxf = Integer.valueOf(formatter.formatCellValue(row.getCell(24)));
        
        return new FinancialManagement(qssj, zlhjlss, dxwt, fzzysj, tcxf);
    }

    /*
     * (non-Javadoc)
     * @see com.ihg.hiex.admin.handler.ParseHandler#parseSwift(java.lang.String, java.lang.String)
     */
    public Swift parseSwift(String delegateEmail, String swiftPath) {
        
        Swift swift = null;
        try {
            
            DataFormatter formatter = new DataFormatter();
            Workbook workbook = WorkbookFactory.create(this.prepareFile(swiftPath));
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rows = sheet.iterator();
            while (rows.hasNext()) {
                
                Row row = rows.next();
                int rowNum = row.getRowNum();
                
                if (rowNum <= 0) {
                    continue;
                }
                
                if(delegateEmail.equalsIgnoreCase(formatter.formatCellValue(row.getCell(1)))) {
                    int verbal = Integer.valueOf(formatter.formatCellValue(row.getCell(12)));
                    int verbalPer = Integer.valueOf(formatter.formatCellValue(row.getCell(13)));
                    int numerical = Integer.valueOf(formatter.formatCellValue(row.getCell(15)));
                    int numericalPer = Integer.valueOf(formatter.formatCellValue(row.getCell(16)));
                    int diagrammatic = Integer.valueOf(formatter.formatCellValue(row.getCell(18)));
                    int diagrammaticPer = Integer.valueOf(formatter.formatCellValue(row.getCell(19)));
                    swift = new Swift(verbal, verbalPer, numerical, numericalPer, diagrammatic, diagrammaticPer);
                }
            }
            
        } catch (Exception e) {
            
            logger.error("##Exception when parse excel.", e);
        }
        
        return swift;
    }
    
    /**
     * request InputStream by path
     * 
     * @param path
     * @return
     */
    protected InputStream prepareFile(String path) {
        
        File file = null;
        
        InputStream inputStream = null;
        
        path = StringUtils.replace(path, cloudHost, cloudPath);
        
        try {
           
            file = ResourceUtils.getFile(path);
            
            inputStream = new FileInputStream(file);
        
        } catch (FileNotFoundException e) {
            
            logger.error("##Exception when find file.", e);
            
        }
        
        return inputStream;
    }
    
}
